import logging
import time

from fastapi import APIRouter, Depends, Query
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

good_class_router = APIRouter(prefix="/materialClass", tags=["耗材管理"], dependencies=[Depends(oauth2_scheme)])


class ClassSearch(PaginationBase):
    fsiteNo: str = Field(None, description="站点编号")
    fclassstname: str = Field(None, description="分类名称")
    fparentno: str = Field(None, description="上级分类")


@good_class_router.get("/list", summary="查询耗材分类信息")
def good_class_list(good: ClassSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select fsite_no,fclassstno,fclassstname,fparentno,fclassstyle,flevel,feffday,fupddate,foperator"
    sql = sql + " from lims.m_ts_material_class where 1=1"
    if good.fsiteNo is not None:
        sql = sql + " and fsite_no = :fsiteNo"
        param["fsiteNo"] = good.fsiteNo
    if good.fclassstname is not None:
        sql = sql + " and fclassstname like concat('%',:fclassstname ,'%')"
        param["fclassstname"] = good.fclassstname
    if good.fparentno is not None:
        sql = sql + " and fparentno = :fparentno "
        param["fparentno"] = good.fparentno
    if good.order == "desc":
        sql = sql + " order by  fclassstno  desc "
    else:
        sql = sql + " order by  fclassstno  asc "
    param["order"] = good.order
    sql = sql + " limit :limit offset :offset"
    param["offset"] = good.offset
    param["limit"] = good.limit

    data = db.execute(text(sql), param).fetchall()

    param = {}
    sql = "select count(1) from lims.m_ts_material_class where 1=1"
    if good.fsiteNo is not None:
        sql = sql + " and fsite_no = :fsiteNo"
        param["fsiteNo"] = good.fsiteNo
    if good.fclassstname is not None:
        sql = sql + " and fclassstname like concat('%',:fclassstname ,'%')"
        param["fclassstname"] = good.fclassstname
    if good.fparentno is not None:
        sql = sql + " and fparentno = :fparentno "
        param["fparentno"] = good.fparentno

    count = db.execute(text(sql), param).scalar()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fsiteno'] = data[key][0]
        d1['fclassstno'] = data[key][1]
        d1['fclassstname'] = data[key][2]
        d1['fparentno'] = data[key][3]
        d1['fclassstyle'] = data[key][4]
        d1['flevel'] = data[key][5]
        d1['feffday'] = data[key][6]
        d1['fupddate'] = data[key][7]
        d1['foperator'] = data[key][8]
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": good.limit, "page": good.pageIndex,
                 "pages": (count + good.limit - 1) // good.limit, "total": count}
    return BaseResponse.success(data=info_dict)


class ClassCreate(BaseModel):
    fclassstno: str = Field(description="物料分类编号")
    fclassstname: str = Field(description="物料分类名")
    fsiteNo: str = Field(description="站点编号")
    fparentno: str = Field(default='0', description="上级分类编号")


@good_class_router.post("/create", summary="新增耗材分类")
def good_class_create(good: ClassCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    user_id = EncryptionMiddleware.decrypt_token(token)

    try:
        with db.begin():
            sql = "select fclassstno from lims.m_ts_material_class where fclassstno= :fclassstno and fsite_No= :fsiteNo"
            result = db.execute(text(sql), {"fclassstno": good.fclassstno, "fsiteNo": good.fsiteNo}).fetchall()
            if len(result) > 0:
                return BaseResponse.error(message="耗材分类编号已存在")

            sql = "insert into lims.m_ts_material_class(fsite_no,fclassstno,fclassstname,fparentno,fclassstyle,flevel,feffday,fupddate,foperator)"
            sql = sql + " values(:fsiteNo,:fclassstno,:fclassstname,:fparentno,0,1,0,now(),:userid);"
            db.execute(text(sql), {
                "fsiteNo": good.fsiteNo,
                "fclassstno": good.fclassstno,
                "fclassstname": good.fclassstname,
                "fparentno": good.fparentno,
                "userid": user_id
            })

            current_datetime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
            d1 = {}
            d1['fsiteno'] = good.fsiteNo
            d1['fclassstno'] = good.fclassstno
            d1['fclassstname'] = good.fclassstname
            d1['fparentno'] = good.fparentno
            d1['fclassstyle'] = 0
            d1['flevel'] = 1
            d1['feffday'] = 0
            d1['fupddate'] = current_datetime
            d1['foperator'] = user_id
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@good_class_router.post("/update", summary="修改耗材分类")
def good_class_update(goods: ClassCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    user_id = EncryptionMiddleware.decrypt_token(token)

    try:
        with db.begin():
            sql = "update lims.m_ts_material_class"
            sql = sql + " set fclassstname= :fclassstname ,fparentno= :fparentno ,fupddate=now(),foperator= :userid"
            sql = sql + " where fclassstno= :fclassstno and fsite_no= :fsiteNo"
            db.execute(text(sql), {
                "fclassstname": goods.fclassstname,
                "fparentno": goods.fparentno,
                "userid": user_id,
                "fclassstno": goods.fclassstno,
                "fsiteNo": goods.fsiteNo
            })
            return BaseResponse.success()

    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


class ClassDelete(BaseModel):
    fsiteno: str = Field(..., description="站点编号")
    fclassstno: str = Field(..., description="分类编号")


@good_class_router.post("/delete", summary="删除耗材分类")
def good_class_delete(goods: ClassDelete, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.decrypt_token(token)
        with db.begin():
            sql = "select sum(f1) "
            sql = sql + " from ( "
            sql = sql + " select count(1) f1 from lims.m_ts_material_class where fparentno= :fclassstno "
            sql = sql + " union"
            sql = sql + " select count(1) f1 from lims.m_ts_material_info where fclassstno= :fclassstno ) a"
            sql = sql + " where 1=1"
            data = db.execute(text(sql), {"fclassstno": goods.fclassstno}).scalar()

            if data is not None and data > 0:
                return BaseResponse.error(code=506, message="该资料在使用，不能删除")
            sql = "delete from lims.m_ts_material_class where fclassstno= :fclassstno and fsite_no= :fsiteNo "
            db.execute(text(sql), {
                "fclassstno": goods.fclassstno,
                "fsiteNo": goods.fsiteno
            })
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
